package com.qst.survey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qst.survey.entity.User;
import com.qst.survey.vo.VM;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Date: 2021/3/19 19:56
 * @Description: 用户映射类
 */

public interface UserMapper extends BaseMapper<User> {
    @Select("SELECT name, COUNT(*) AS value\n" +
            "FROM (\n" +
            "         SELECT user.age,\n" +
            "                CASE\n" +
            "                    WHEN age BETWEEN 0 AND 18 THEN '18岁以下'\n" +
            "                    WHEN age BETWEEN 18 AND 30 THEN '18-30'\n" +
            "                    WHEN age BETWEEN 31 AND 40 THEN '31-40'\n" +
            "                    WHEN age BETWEEN 41 AND 50 THEN '41-50'\n" +
            "                    WHEN age BETWEEN 51 AND 60 THEN '51-60'\n" +
            "                    END AS name\n" +
            "         FROM user_reply,\n" +
            "              user\n" +
            "         where user_reply.user_id = user.id\n" +
            "           and question_id = #{id}\n" +
            "     ) AS USER\n" +
            "GROUP BY name;")
    public List<VM> findAge(Integer id);

    @Select("SELECT name, COUNT(*) AS value\n" +
            "FROM (\n" +
            "         SELECT user.sex,\n" +
            "                CASE\n" +
            "                   WHEN sex=0 then '女'\n" +
            "                   WHEN sex=1 then '男'\n" +
            "                    END AS name\n" +
            "         FROM user_reply,\n" +
            "              user\n" +
            "         where user_reply.user_id = user.id\n" +
            "           and question_id = #{id}\n" +
            "     ) AS USER\n" +
            "GROUP BY name;")
    public List<VM> findSex(Integer id);
}
